﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using liaowa2.Entity.DO;
using liaowa2.Entity.DTO.Article;
using System.Data;
using Dapper;

namespace liaowa2.Dao.Web
{
    public class ArticleDao : BaseRepository<Article>
    {
        public virtual IEnumerable<ListOutputDTO> List(int offset, int next, string st, string et, int cate, string kw,out int count)
        {
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append($"select T.*,C.title As CategoryName ");
                strSql.Append($" from Article as T left join ArticleCategory as C on T.category = C.id");
                strSql.Append($" where 1=1");

                if (!string.IsNullOrEmpty(st) && !string.IsNullOrEmpty(et))
                {
                    strSql.Append($" and (T.CreateTime > {st} and T.CreateTime  < et)");
                }

                if (cate > 0)
                {
                    strSql.Append($" and T.Category = {cate}");
                }

                if (!string.IsNullOrEmpty(kw))
                {
                    strSql.Append($" and T.Title like '%{kw}% '");
                }
                strSql.Append(" order by desc T.Id");

                object total = conn.ExecuteScalar(strSql.ToString());
                count = Convert.ToInt32(total);
                strSql.Append($" offset {offset} row fetch next {next} rows only");
                IEnumerable<ListOutputDTO> result = conn.Query<ListOutputDTO>(strSql.ToString(), commandType: CommandType.Text);
                return result;
            }
        }
    }
}
